package com.lms.logistics_management_system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lms.logistics_management_system.controller.dto.DepartmentAndItemDetailDto;
import com.lms.logistics_management_system.controller.dto.DepartmentAndItemDto;
import com.lms.logistics_management_system.controller.dto.DepartmentRankDetailDto;
import com.lms.logistics_management_system.entity.QueueCondition;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.ArrayList;
import java.util.List;

@Mapper
public interface QueueConditionMapper extends BaseMapper<QueueCondition> {

    @Select("SELECT * FROM queue_condition WHERE reservation_id=#{reservationId}")
    QueueCondition getByReservationId(Integer reservationId);

    @Select("SELECT * FROM queue_condition WHERE reservation_id=#{reservationId} AND department_id!=0 AND room_id!=0")
    QueueCondition getQueueByReservationId(Integer reservationId);

    boolean updateQueueCondition(QueueCondition queueCondition);

    @Select("SELECT COUNT(reservation_id) FROM queue_condition WHERE department_id=#{departmentId} AND room_id=#{roomId}")
    Integer queueCount(Integer departmentId, Integer roomId);

    @Select("SELECT COUNT(q1.reservation_id) \n" +
            "FROM queue_condition q1, (SELECT * FROM queue_condition \n" +
            "WHERE reservation_id = #{reservationId}) q2\n" +
            "WHERE q1.department_id != 0 \n" +
            "AND q1.room_id != 0 \n" +
            "AND q1.department_id = q2.department_id\n" +
            "AND q1.department_id = q2.department_id\n" +
            "AND q1.queue_condition_entry_time < q2.queue_condition_entry_time")
    Integer queueAheadCount(Integer reservationId);

    @Select("SELECT * FROM queue_condition WHERE department_id=1 AND room_id=1 AND\n" +
            "queue_condition_entry_time=(SELECT MIN(queue_condition_entry_time) FROM queue_condition WHERE\n" +
            "department_id=#{departmentId} AND room_id=#{roomId})")
    QueueCondition getFirst(Integer departmentId, Integer roomId);

    @Select("(SELECT d.department_name, \n" +
            "(SELECT COUNT(*) FROM queue_condition q WHERE \n" +
            "q.department_id=d.department_id AND q.department_id!=0 AND q.room_id!=0) queue_sum,\n" +
            "i.item_name\n" +
            "FROM department d, item i, reservation_item r\n" +
            "WHERE r.item_id=i.item_id\n" +
            "AND r.reservation_id=1\n" +
            "AND r.reservation_item_result_comeout=0\n" +
            "AND d.department_id=i.department_id\n" +
            "AND d.department_id!=0)\n" +
            "UNION\n" +
            "(SELECT d1.department_name,\n" +
            "(SELECT COUNT(*) FROM queue_condition q WHERE \n" +
            "q.department_id!=0 \n" +
            "AND q.room_id!=0 \n" +
            "AND d1.department_id=q.department_id) queue_sum,\n" +
            "NULL\n" +
            "FROM department d1\n" +
            "WHERE d1.department_id!=0 \n" +
            "AND d1.department_id not in \n" +
            "(SELECT DISTINCT d.department_id FROM department d, reservation_item r, item i \n" +
            "WHERE d.department_id=i.department_id \n" +
            "AND r.reservation_id=1\n" +
            "AND r.item_id = i.item_id\n" +
            "AND r.reservation_item_result_comeout=0))")
    ArrayList<DepartmentRankDetailDto> allDepartmentRank(Integer reservationId);

    @Select("SELECT d.department_id, d.department_name, \n" +
            "i.item_id, i.item_name, i.item_price\n" +
            "FROM department d, item i, reservation_item r\n" +
            "WHERE r.item_id = i.item_id\n" +
            "AND r.reservation_id = #{reservationId}\n" +
            "AND r.reservation_item_result_comeout=0\n" +
            "AND d.department_id=i.department_id\n")
    ArrayList<DepartmentAndItemDetailDto> allDepartmentAndItemDetail(Integer reservationId);

}
